<?php
        date_default_timezone_set('Asia/Jakarta');

        $set_con = 1;
        $set_wrt = 1;
        $CAR = '00000000646720140710003446';
        
        if($set_con==1) {
                // open connection to database
                // mysqli_connect(host,username,password,dbname);
                $con = mysqli_connect("localhost","root","","test_export");

                // Check connection
                if (mysqli_connect_errno()) {
                  echo "Failed to connect to MySQL: " . mysqli_connect_error();
                }

                //while($row = mysqli_fetch_array($result)) {
                        //echo $row['FirstName'] . " " . $row['LastName'];
                        //echo "<br>";
                //}
        }
        
        if($set_wrt==1) {

                // init
                $prefix = "KWEID_";
                $year = date("Y");
                $month = date("m");
                $date = date("d");
                $separator = "-";
                $hour = date("H");
                $minute = date("i");
                $counter_num = 1;
                $counter_pad = str_pad(($counter_num), 2, "0", STR_PAD_LEFT);
                $file_extension = ".EDI";

                $filename = $prefix.$year.$month.$date.$separator.$hour.$minute.$counter_pad.$file_extension;
                //$filename = "KWETW_20140425-100759.EDI";
                
                // increment counter check
                while (file_exists($filename)) {
                        $counter_num += 1;
                        $counter_pad = str_pad(($counter_num), 2, "0", STR_PAD_LEFT);
                        $filename = $prefix.$year.$month.$date.$separator.$hour.$minute.$counter_pad.$file_extension;
                }
                
                // begin open or create file
                $myfile = fopen($filename, "w") or die("Unable to open file ".$filename." !");
                
                $headerresult = mysqli_query($con,"SELECT * FROM headers WHERE CAR = '" . $CAR . "'");
                $detailresult = mysqli_query($con,"SELECT * FROM details WHERE CAR = '" . $CAR . "'");
                $invoiceCounter = 0;
                $segmentCounter = 0;
                $generatedate = date('ymd');
                $generatetime = date('hi');
                $unhnumber = $generatedate . $generatetime . $counter_num;

                // begin writing text into file
                $headerrow = mysqli_fetch_array($headerresult);
                
                $txt = "";
                
                $txt .= "UNA:+.? '";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "UNB+UNOA:3+KWEIDT:1+003897733T:1+" . $generatedate . ":" . $generatetime . "+4208++++++1'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "UNH+" . $unhnumber . "+CUSRES:D:09B:UN'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "BGM+914+" . $headerrow['EntryNumber'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "GEI+4+23:::" . $headerrow['CustomsRegime'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "NAD+BO+" . $headerrow['BrokerId'] . "+" . $headerrow['BrokerName'] . "+" . $headerrow['VendorCode'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+ACD:" . $headerrow['ImportExport'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+ADZ:" . $headerrow['IOR'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+FO:" . $headerrow['ExchangeRate'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+ABS:" . $headerrow['EntryType'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+AQK:" . $headerrow['DeclarationMessageStatus'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+VA:" . $headerrow['VatImporter'] . "'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+11:" . $headerrow['ShipDate'] . ":3'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+11:" . $headerrow['ShipTime'] . ":402'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+108:" . $headerrow['DateOfGoodsArrival'] . ":3'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+108:" . $headerrow['TimeOfGoodsArrival'] . ":402'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+151:" . $headerrow['ImportDecDate'] . ":3'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+151:" . $headerrow['ImportDecTime'] . ":402'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "DTM+416:" . $headerrow['DateTimeSubmissionEntry'] . ":202'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "LOC+28+" . $headerrow['CountryDest'] . "::5'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "LOC+105++" . $headerrow['PlaceOfImportation'] . "::5'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "ERP+1'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "RFF+VA'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "TAX+9'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                $txt = "MOA+81:" . $headerrow['TotalLogisticCharge'] . ":" . $headerrow['InvoiceCurrencyName'] . ":1'";
                fwrite($myfile, $txt);
                $segmentCounter++;
                
                while($detailrow = mysqli_fetch_array($detailresult))
                {
                    $invoiceCounter++;
                    
                    $txt = "DOC+961'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "PCI+17'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "FTX+AAA+++" . $detailrow['ZDescription'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "TDT+11+++::182:" . $detailrow['SCACCode'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "LOC+35+" . $detailrow['CountryDeparture'] . "::5'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "LOC+27+" . $detailrow['ProductCountryOrigin'] . "::5'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "DTM+310:" . $detailrow['BrokerInvoiceDate'] . ":3'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "DTM+310:" . $detailrow['BrokerInvoiceTime'] . ":402'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "CST'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "FTX+LIN'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "TAX+7+VAT:" . $detailrow['CurrencyImportVAT'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "TAX+9+VAT:::" . $detailrow['USDDutyAmount'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "TAX+1+CUD:::" . $detailrow['DutyChargeAmount'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "MOA+77:" . $detailrow['ItemTotalValue'] . ":" . $detailrow['CurrencyItemTotalValue'] . ":4'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "MOA+146:" . $detailrow['UnitPrice'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "MOA+10:" . $detailrow['ImportTaxRate'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "MOA+150:" . $detailrow['ImportTaxAmount'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "MEA+AAR++UN:" . $detailrow['Quantity'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+OH:" . $detailrow['InvoiceNumber'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+IV:" . $detailrow['InvoiceLevel'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+AVU:" . $detailrow['ShipRef'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+UO:" . $detailrow['SalesOrder'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+ABT:" . $detailrow['CustomsDeclaration'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+AOG:" . $detailrow['LegacyOrderNumber'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+ON:" . $detailrow['PONumber'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+MWB:" . $detailrow['MasterAirwayBill'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+BM:" . $detailrow['BOLNumber'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "TAX+5+++" . $detailrow['DutyRate'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+BH:" . $detailrow['HouseAirwayBill'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+CW:" . $detailrow['PackID'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+MF:ExternalIDProduct'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+ABD:" . $detailrow['CustomsTariffNo'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+AVB:" . $detailrow['GCTOID'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+ADA:" . $detailrow['InvoiceLineItem'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+AFD:" . $detailrow['EntryDeclarationLineItem'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "RFF+AEO:" . $detailrow['ProductLine'] . "'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "ERP+9'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                    
                    $txt = "FTX+AAV'";
                    fwrite($myfile, $txt);
                    $segmentCounter++;
                }
                
                $txt = "CNT+3:" . $invoiceCounter . "'";
                fwrite($myfile, $txt);
                
                $txt = "UNT+" . $segmentCounter . "+" . $unhnumber . "'";
                fwrite($myfile, $txt);
                
                $txt = "UNZ+1+4208'";
                fwrite($myfile, $txt);
                
                // close file
                fclose($myfile);
                
                if(file_exists($filename)) { echo "file : ".$filename." has been created !"; }
        }

        if($set_con==1) {
                // close connection
                mysqli_close($con);
        }
?>